-- Create outProdPaper table
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'OutProdPaper') and OBJECTPROPERTY(ID, N'IsUserTable') = 1) BEGIN

   CREATE TABLE [OutProdPaper] (
   	[Id]					INT IDENTITY (1, 1) NOT NULL ,
   	[Date]					DATETIME NOT NULL ,
	[CreatedBy]				INT NULL,
	[ToShopId]				INT NULL,
	[ReferInProdPaperId]	INT NULL
   
   	CONSTRAINT [rpk_OutProdPaper] PRIMARY KEY NONCLUSTERED 
   	(
   		[Id]
   	)  ON [PRIMARY] ,
   ) ON [PRIMARY]
   
	ALTER TABLE dbo.OutProdPaper
	ADD CONSTRAINT rfk_OutProdPaper_Shop_ToShopId FOREIGN KEY
		( ToShopId ) 
	REFERENCES dbo.Shop
		( Id )
		
	SET IDENTITY_INSERT dbo.OutProdPaper ON
	

	-- Insert old output to new output tables
	INSERT INTO OutProdPaper (
		Id,
		Date,
		CreatedBy,
		ToShopId
	) SELECT
		Id,
		Date,
		CreatedBy,
		ToShopId
	FROM InOutProdPaper
	WHERE [Type] = 1

	SET IDENTITY_INSERT dbo.OutProdPaper OFF
	
END

-- Create outProdDetail table
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'OutProdDetail') and OBJECTPROPERTY(ID, N'IsUserTable') = 1) BEGIN

   CREATE TABLE [OutProdDetail] (
   	[Id]					INT IDENTITY (1, 1) NOT NULL ,
   	[ProductId]				INT NOT NULL,
   	[Amount]				INT NOT NULL,
   	[OutProdPaperId]		INT NOT NULL,
   	[ReferInProdDetailId]	INT NULL
   
      -- Create non-clustered primary key
   	CONSTRAINT [rpk_OutProdDetail] PRIMARY KEY NONCLUSTERED 
   	(
   		[Id]
   	)  ON [PRIMARY] ,
   ) ON [PRIMARY]
   
	ALTER TABLE dbo.OutProdDetail
	ADD CONSTRAINT rfk_OutProdDetail_Product_ProductId FOREIGN KEY
		( ProductId ) 
	REFERENCES dbo.Product
		( Id )
	ON DELETE CASCADE
		
	ALTER TABLE dbo.OutProdDetail
	ADD CONSTRAINT rfk_OutProdDetail_OutProPaper_OutProdPaperId FOREIGN KEY
		( OutProdPaperId ) 
	REFERENCES dbo.OutProdPaper
		( Id )
	ON DELETE CASCADE
	
	-- Insert old data to out product detail table
	SET IDENTITY_INSERT dbo.OutProdDetail ON

	INSERT INTO OutProdDetail (
		Id,
		ProductId,
		Amount,
		OutProdPaperId
	) SELECT
		InOutProdDetail.Id,
		InOutProdDetail.ProductId,
		InOutProdDetail.Amount,
		InOutProdDetail.InOutProdPaperId
	FROM InOutProdDetail
	INNER JOIN InOutProdPaper
	ON InOutProdDetail.InOutProdPaperId = InOutProdPaper.Id
	AND InOutProdPaper.[Type] = 1

	SET IDENTITY_INSERT dbo.OutProdDetail OFF
END

-- Change name and update inprod detail table
-- Delete old out prod detail
DELETE
FROM InOutProdDetail
WHERE InOutProdDetail.InputPrice IS NULL

-- Delete old out prod paper
DELETE FROM InOutProdPaper
WHERE InOutProdPaper.[Type] = 1

EXEC sp_rename 'InOutProdDetail', 'InProdDetail'
EXEC sp_rename @objname = 'InProdDetail.InOutProdPaperId',
    @newname = 'InProdPaperId',
    @objtype = 'COLUMN'

EXEC sp_rename 'InOutProdPaper', 'InProdPaper'
ALTER TABLE InProdPaper DROP COLUMN [Type]
ALTER TABLE InProdPaper DROP rfk_InOutProdPaper_FromShop_FKID
ALTER TABLE InProdPaper DROP COLUMN FromShopId
ALTER TABLE InProdPaper DROP rfk_InOutProdPaper_ToShop_FKID
ALTER TABLE InProdPaper DROP COLUMN ToShopId
ALTER TABLE InProdPaper DROP FK_InOutProdPaper_User

